from sqlalchemy import CHAR, BigInteger, Column, DateTime, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from app.models.base import BaseModel, SoftDeleteMixin, TenantMixin


class User(BaseModel, TenantMixin, SoftDeleteMixin):
    """用户模型 - 匹配sys_user表结构"""

    __tablename__ = "sys_user"

    user_id = Column(BigInteger, primary_key=True, comment="用户ID")
    dept_id = Column(BigInteger, comment="部门ID")
    user_name = Column(String(30), nullable=False, comment="用户账号")
    nick_name = Column(String(30), nullable=False, comment="用户昵称")
    user_type = Column(String(10), default="sys_user", comment="用户类型（sys_user系统用户）")
    email = Column(String(50), default="", comment="用户邮箱")
    phonenumber = Column(String(11), default="", comment="手机号码")
    sex = Column(CHAR(1), default="0", comment="用户性别（0男 1女 2未知）")
    avatar = Column(BigInteger, comment="头像地址")
    password = Column(String(100), default="", comment="密码")
    status = Column(CHAR(1), default="0", comment="帐号状态（0正常 1停用）")
    login_ip = Column(String(128), default="", comment="最后登录IP")
    login_date = Column(DateTime, comment="最后登录时间")

    # 关联关系
    dept = relationship("Dept", back_populates="users")
    roles = relationship("Role", secondary="sys_user_role", back_populates="users")
    posts = relationship("Post", secondary="sys_user_post", back_populates="users")


class Role(BaseModel, TenantMixin, SoftDeleteMixin):
    """角色模型 - 匹配sys_role表结构"""

    __tablename__ = "sys_role"

    role_id = Column(BigInteger, primary_key=True, comment="角色ID")
    role_name = Column(String(30), nullable=False, comment="角色名称")
    role_key = Column(String(100), nullable=False, comment="角色权限字符串")
    role_sort = Column(Integer, nullable=False, comment="显示顺序")
    data_scope = Column(
        CHAR(1), default="1", comment="数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）"
    )
    menu_check_strictly = Column(Integer, default=1, comment="菜单树选择项是否关联显示")
    dept_check_strictly = Column(Integer, default=1, comment="部门树选择项是否关联显示")
    status = Column(CHAR(1), nullable=False, comment="角色状态（0正常 1停用）")

    # 关联关系
    users = relationship("User", secondary="sys_user_role", back_populates="roles")
    menus = relationship("Menu", secondary="sys_role_menu", back_populates="roles")


class Dept(BaseModel, TenantMixin, SoftDeleteMixin):
    """部门模型 - 匹配sys_dept表结构"""

    __tablename__ = "sys_dept"

    dept_id = Column(BigInteger, primary_key=True, comment="部门id")
    parent_id = Column(BigInteger, default=0, comment="父部门id")
    ancestors = Column(String(500), default="", comment="祖级列表")
    dept_name = Column(String(30), default="", comment="部门名称")
    dept_category = Column(String(100), comment="部门类别编码")
    order_num = Column(Integer, default=0, comment="显示顺序")
    leader = Column(BigInteger, comment="负责人")
    phone = Column(String(11), comment="联系电话")
    email = Column(String(50), comment="邮箱")
    status = Column(CHAR(1), default="0", comment="部门状态（0正常 1停用）")

    # 关联关系
    users = relationship("User", back_populates="dept")


class Post(BaseModel, TenantMixin, SoftDeleteMixin):
    """岗位模型 - 匹配sys_post表结构"""

    __tablename__ = "sys_post"

    post_id = Column(BigInteger, primary_key=True, comment="岗位ID")
    dept_id = Column(BigInteger, nullable=False, comment="部门id")
    post_code = Column(String(64), nullable=False, comment="岗位编码")
    post_category = Column(String(100), comment="岗位类别编码")
    post_name = Column(String(50), nullable=False, comment="岗位名称")
    post_sort = Column(Integer, nullable=False, comment="显示顺序")
    status = Column(CHAR(1), nullable=False, comment="状态（0正常 1停用）")

    # 关联关系
    users = relationship("User", secondary="sys_user_post", back_populates="posts")


# 关联表
from sqlalchemy import Table

from app.models.base import Base

# 用户角色关联表
sys_user_role = Table(
    "sys_user_role",
    Base.metadata,
    Column(
        "user_id",
        BigInteger,
        ForeignKey("sys_user.user_id"),
        primary_key=True,
        comment="用户ID",
    ),
    Column(
        "role_id",
        BigInteger,
        ForeignKey("sys_role.role_id"),
        primary_key=True,
        comment="角色ID",
    ),
)

# 用户岗位关联表
sys_user_post = Table(
    "sys_user_post",
    Base.metadata,
    Column(
        "user_id",
        BigInteger,
        ForeignKey("sys_user.user_id"),
        primary_key=True,
        comment="用户ID",
    ),
    Column(
        "post_id",
        BigInteger,
        ForeignKey("sys_post.post_id"),
        primary_key=True,
        comment="岗位ID",
    ),
)

# 角色菜单关联表
sys_role_menu = Table(
    "sys_role_menu",
    Base.metadata,
    Column(
        "role_id",
        BigInteger,
        ForeignKey("sys_role.role_id"),
        primary_key=True,
        comment="角色ID",
    ),
    Column(
        "menu_id",
        BigInteger,
        ForeignKey("sys_menu.menu_id"),
        primary_key=True,
        comment="菜单ID",
    ),
)
